﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Reflection;
using System.Data;
using System.Dynamic;

namespace DBUtility
{
    public class MySqlHelper
    {
        private static string ConnectionString = "server=127.0.0.1; user id=root; password=123456; database=workorder; pooling=true;CharSet=utf8;SslMode=None;";
        /// <summary>
        ///初始化MySqlHelper实例
        /// </summary>
        public MySqlHelper()
        {
            
        }


        #region 实例方法

        #region 执行分页SQL

        /// <summary>
        /// Dynamic类型的分页
        /// </summary>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="pageCount">每页条数</param>
        /// <param name="totalPages">总页数</param>
        /// <param name="totalItems">总条数</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static List<dynamic> ExecuteDynamicPage(CommandType commandType, string commandText, int pageCount, ref int totalPages, ref int totalItems, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                MySqlDataReader reader = ExecuteDataReader(connection, commandType, commandText, parms);

                List<dynamic> list = new List<dynamic>();
                if (reader != null && !reader.IsClosed)
                {
                    while (reader.Read())
                    {
                        list.Add(DataFillDynamic(reader));
                    }
                }

                bool isNext = reader.NextResult();
                if (reader.Read() && isNext)
                {
                    totalItems = Convert.ToInt32(reader.GetValue(0));
                }
                totalPages = totalItems % pageCount == 0 ? totalItems / pageCount : totalItems / pageCount + 1;

                reader.Close();
                return list;
            }
            
        }

        /// <summary>
        /// Dynamic类型的分页
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="pageCount">每页条数</param>
        /// <param name="totalPages">总页数</param>
        /// <param name="totalItems">总条数</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static List<dynamic> ExecuteDynamicPage(string commandText, int pageCount, ref int totalPages, ref int totalItems, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                MySqlDataReader reader = ExecuteDataReader(connection, CommandType.Text, commandText, parms);

                List<dynamic> list = new List<dynamic>();
                if (reader != null && !reader.IsClosed)
                {
                    while (reader.Read())
                    {
                        list.Add(DataFillDynamic(reader));
                    }
                }

                bool isNext = reader.NextResult();
                if (reader.Read() && isNext)
                {
                    totalItems = Convert.ToInt32(reader.GetValue(0));
                }
                totalPages = totalItems % pageCount == 0 ? totalItems / pageCount : totalItems / pageCount + 1;

                reader.Close();
                return list;
            }
        }


        /// <summary>
        /// 实体分页
        /// </summary>
        /// <typeparam name="T">返回对象类型</typeparam>
        /// <param name="commandText">SQL语句</param>
        /// <param name="pageCount">每页条数</param>
        /// <param name="totalPages">总页数</param>
        /// <param name="totalItems">总条数</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static List<T> ExecuteDataReaderPage<T>(string commandText, int pageCount, ref int totalPages, ref int totalItems, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                MySqlDataReader reader = ExecuteDataReader(connection, CommandType.Text, commandText, parms);
                List<T> list = DataReaderToObj<T>(reader);

                bool isNext = reader.NextResult();
                if (reader.Read() && isNext)
                {
                    totalItems = Convert.ToInt32(reader.GetValue(0));
                }
                totalPages = totalItems % pageCount == 0 ? totalItems / pageCount : totalItems / pageCount + 1;
                reader.Close();
                return list;
            }
        }

        /// <summary>
        /// 实体分页
        /// </summary>
        /// <typeparam name="T">返回对象类型</typeparam>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="pageCount">每页条数</param>
        /// <param name="totalPages">总页数</param>
        /// <param name="totalItems">总条数</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static List<T> ExecuteDataReaderPage<T>(CommandType commandType, string commandText, int pageCount, ref int totalPages, ref int totalItems, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                MySqlDataReader reader = ExecuteDataReader(connection, commandType, commandText, parms);
                List<T> list = DataReaderToObj<T>(reader);

                bool isNext = reader.NextResult();
                if (reader.Read() && isNext)
                {
                    totalItems = Convert.ToInt32(reader.GetValue(0));
                }
                totalPages = totalItems % pageCount == 0 ? totalItems / pageCount : totalItems / pageCount + 1;
                reader.Close();
                return list;
            }
        }
        #endregion

        #region ExecuteDynamic
        /// <summary>
        /// 将DataReader数据转为Dynamic对象
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static dynamic DataFillDynamic(IDataReader reader)
        {
            dynamic d = new ExpandoObject();
            IDictionary<string, object> dic = (IDictionary<string, object>)d;
            for (int i = 0; i < reader.FieldCount; i++)
            {
                try
                {
                    if (dic.ContainsKey(reader.GetName(i)))
                    {
                        continue;
                    }
                    if (reader.IsDBNull(i))
                    {
                        dic.Add(reader.GetName(i), null);
                    }
                    else
                    {
                        dic.Add(reader.GetName(i), reader.GetValue(i));
                    }
                }
                catch
                {
                    dic.Add(reader.GetName(i), null);
                }
            }
            return d;
        }

        /// <summary>
        /// 获取模型对象集合
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static List<dynamic> DataFillDynamicList(IDataReader reader)
        {
            List<dynamic> list = new List<dynamic>();
            if (reader != null && !reader.IsClosed)
            {
                while (reader.Read())
                {
                    list.Add(DataFillDynamic(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static List<dynamic> ExecuteDynamic(string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                return DataFillDynamicList(ExecuteDataReader(connection, CommandType.Text, commandText, parms));
            }
        }

        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public List<dynamic> ExecuteDynamic(CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                return DataFillDynamicList(ExecuteDataReader(connection, commandType, commandText, parms));
            }
        }
        #endregion

        #region ExecuteDataReaderModel
        /// <summary>
        /// 执行SQL语句,返回一条数据集
        /// </summary>
        /// <typeparam name="T">返回对象类型</typeparam>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static T ExecuteDataReaderModel<T>(string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                List<T> list = DataReaderToObj<T>(ExecuteDataReader(connection, CommandType.Text, commandText, parms));
                if (list.Count > 0)
                {
                    return list[0];
                }
                else
                {
                    return default(T);
                }
            }
        }

        /// <summary>
        /// 执行SQL语句,返回一条数据集
        /// </summary>
        /// <typeparam name="T">返回对象类型</typeparam>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static T ExecuteDataReaderModel<T>(CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                List<T> list = DataReaderToObj<T>(ExecuteDataReader(connection, commandType, commandText, parms));
                if (list.Count > 0)
                {
                    return list[0];
                }
                else
                {
                    return default(T);
                }
            }
        }
        #endregion

        #region ExecuteNonQuery

        /// <summary>
        /// 执行SQL语句,返回影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(string commandText, params MySqlParameter[] parms)
        {
            return ExecuteNonQuery(ConnectionString, CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 执行SQL语句,返回影响的行数
        /// </summary>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            return ExecuteNonQuery(ConnectionString, commandType, commandText, parms);
        }

        #endregion ExecuteNonQuery

        #region ExecuteScalar

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <typeparam name="T">返回对象类型</typeparam>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static T ExecuteScalar<T>(string commandText, params MySqlParameter[] parms)
        {
            return ExecuteScalar<T>(ConnectionString, commandText, parms);
        }

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(string commandText, params MySqlParameter[] parms)
        {
            return ExecuteScalar(ConnectionString, CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        public static object ExecuteScalar(CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            return ExecuteScalar(ConnectionString, commandType, commandText, parms);
        }

        #endregion ExecuteScalar

        #region ExecuteDataReader

        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static MySqlDataReader ExecuteDataReader(string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                return ExecuteDataReader(connection, CommandType.Text, commandText, parms);
            }
        }

        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <typeparam name="T">返回对象类型</typeparam>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static List<T> ExecuteDataReader<T>(string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                return DataReaderToObj<T>(ExecuteDataReader(connection, CommandType.Text, commandText, parms));
            }
        }

        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static MySqlDataReader ExecuteDataReader(CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                return ExecuteDataReader(connection, commandType, commandText, parms);
            }
        }

        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <typeparam name="T">返回对象类型</typeparam>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        public static List<T> ExecuteDataReader<T>(CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(ConnectionString))
            {
                return DataReaderToObj<T>(ExecuteDataReader(connection, commandType, commandText, parms));
            }
        }
        #endregion

        #endregion

        #region 私有方法

        /// <summary>  
        /// DataReader转换为obj list  
        /// </summary>  
        /// <typeparam name="T">泛型</typeparam>  
        /// <param name="rdr">datareader</param>  
        /// <returns>返回泛型类型</returns>  
        private static List<T> DataReaderToObj<T>(MySqlDataReader rdr)
        {
            List<T> list = new List<T>();

            while (rdr.Read())
            {
                T t = System.Activator.CreateInstance<T>();
                Type obj = t.GetType();
                // 循环字段  
                for (int i = 0; i < rdr.FieldCount; i++)
                {
                    object tempValue = null;
                    PropertyInfo p = obj.GetProperty(rdr.GetName(i));
                    if (p == null)
                    {
                        continue;
                    }
                    if (rdr.IsDBNull(i))
                    {

                        string typeFullName = p.PropertyType.FullName;
                        tempValue = GetDBNullValue(typeFullName);

                    }
                    else
                    {

                        if (p.PropertyType.Name == TypeCode.Boolean.ToString())
                        {
                            tempValue = Convert.ToBoolean(rdr.GetValue(i));
                        }
                        else
                        {
                            tempValue = rdr.GetValue(i);
                        }
                    }

                    p.SetValue(t, tempValue, null);

                }

                list.Add(t);

            }
            return list;
        }

        /// <summary>  
        /// 返回值为DBnull的默认值  
        /// </summary>  
        /// <param name="typeFullName">数据类型的全称，类如：system.int32</param>  
        /// <returns>返回的默认值</returns>  
        private static object GetDBNullValue(string typeFullName)
        {

            typeFullName = typeFullName.ToLower();

            if (typeFullName == "system.string")
            {
                return String.Empty;
            }
            if (typeFullName == "system.int32")
            {
                return 0;
            }
            if (typeFullName == "system.int64")
            {
                return 0;
            }
            if (typeFullName == "system.boolean")
            {
                return false;
            }
            if (typeFullName == "system.int")
            {
                return 0;
            }

            return null;
        }



        private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] parms)
        {
            if (connection.State != ConnectionState.Open) connection.Open();

            command.Connection = connection;
            command.CommandTimeout = 600;
            // 设置命令文本(存储过程名或SQL语句)
            command.CommandText = commandText;
            // 分配事务
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            // 设置命令类型.
            command.CommandType = commandType;
            if (parms != null && parms.Length > 0)
            {
                //预处理MySqlParameter参数数组，将为NULL的参数赋值为DBNull.Value;
                foreach (MySqlParameter parameter in parms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                }
                command.Parameters.AddRange(parms);
            }
        }

        #region ExecuteNonQuery

        /// <summary>
        /// 执行SQL语句,返回影响的行数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回影响的行数</returns>
        private static int ExecuteNonQuery(string connectionString, string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return ExecuteNonQuery(connection, CommandType.Text, commandText, parms);
            }
        }

        /// <summary>
        /// 执行SQL语句,返回影响的行数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回影响的行数</returns>
        private static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return ExecuteNonQuery(connection, commandType, commandText, parms);
            }
        }

        /// <summary>
        /// 执行SQL语句,返回影响的行数
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回影响的行数</returns>
        private static int ExecuteNonQuery(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            return ExecuteNonQuery(connection, null, commandType, commandText, parms);
        }

        /// <summary>
        /// 执行SQL语句,返回影响的行数
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回影响的行数</returns>
        private static int ExecuteNonQuery(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            return ExecuteNonQuery(transaction.Connection, transaction, commandType, commandText, parms);
        }

        /// <summary>
        /// 执行SQL语句,返回影响的行数
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回影响的行数</returns>
        private static int ExecuteNonQuery(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            MySqlCommand command = new MySqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, parms);
            int retval = command.ExecuteNonQuery();
            command.Parameters.Clear();
            return retval;
        }

        #endregion ExecuteNonQuery

        #region ExecuteScalar

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <typeparam name="T">返回对象类型</typeparam>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        private static T ExecuteScalar<T>(string connectionString, string commandText, params MySqlParameter[] parms)
        {
            object result = ExecuteScalar(connectionString, commandText, parms);
            if (result != null)
            {
                return (T)Convert.ChangeType(result, typeof(T)); ;
            }
            return default(T);
        }

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        private static object ExecuteScalar(string connectionString, string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return ExecuteScalar(connection, CommandType.Text, commandText, parms);
            }
        }

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        private static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                return ExecuteScalar(connection, commandType, commandText, parms);
            }
        }

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        private static object ExecuteScalar(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            return ExecuteScalar(connection, null, commandType, commandText, parms);
        }

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        private static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            return ExecuteScalar(transaction.Connection, transaction, commandType, commandText, parms);
        }

        /// <summary>
        /// 执行SQL语句,返回结果集中的第一行第一列
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        private static object ExecuteScalar(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            MySqlCommand command = new MySqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, parms);
            object retval = command.ExecuteScalar();
            command.Parameters.Clear();
            return retval;
        }

        #endregion ExecuteScalar

        #region ExecuteDataReader


        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            return ExecuteDataReader(connection, null, commandType, commandText, parms);
        }

        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        private static MySqlDataReader ExecuteDataReader(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            return ExecuteDataReader(transaction.Connection, transaction, commandType, commandText, parms);
        } 

        /// <summary>
        /// 执行SQL语句,返回只读数据集
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回只读数据集</returns>
        private static MySqlDataReader ExecuteDataReader(MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            MySqlCommand command = new MySqlCommand();
            PrepareCommand(command, connection, transaction, commandType, commandText, parms);
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion

        #endregion
    }
}
